﻿using iTextSharp.text;
using iTextSharp.text.pdf;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Net;
using System.Text;
using System.Web.Mvc;
using System.Web.UI;
using System.Web.UI.HtmlControls;

namespace eLoan.Models
{
    public static class ReportHelper
    {
        public static IEnumerable<T> GetRecievablesToDate<T>(DateTime date, string branchCode, int agentID)
        {
            var recievablesToDate = new List<T>();

            using (var db = new DatabaseContext())
            {
                var paramDate = new SqlParameter("@ToDate", date);
                var paramBranchCode = new SqlParameter("@BranchCode", branchCode);
                var paramAgentID = new SqlParameter("@AgentID", agentID);

                recievablesToDate = db.Database
                    .SqlQuery<T>("sp_GetRecievablesToDate @ToDate, @BranchCode, @AgentID",
                                                                    paramDate, paramBranchCode, paramAgentID)
                    .ToList();
            }

            return recievablesToDate;

        }

        public static List<BatchPaymentItem> GetPaymentItems(DateTime toDate, string branchCode, int agentID, string searchText)
        {
            var items = new List<BatchPaymentItem>();

            using (var db = new DatabaseContext())
            {
                var paramDate = new SqlParameter("@ToDate", toDate);
                var paramBranchCode = new SqlParameter("@BranchCode", branchCode);
                var paramAgentID = new SqlParameter("@AgentID", agentID);
                var paramSearchText = new SqlParameter("@SearchText", searchText);

                items = db.Database
                    .SqlQuery<BatchPaymentItem>("sp_GetLoansForPayment @ToDate, @BranchCode, @AgentID , @SearchText",
                                                                    paramDate, paramBranchCode, paramAgentID, paramSearchText)
                    .ToList();
            }

            return items;
        }

        public static AgentItineraryModel GetItineraryModel(DatabaseContext db, string userName, string branchCode, int? agentID, string searchText)
        {
            var model = new AgentItineraryModel();

            branchCode = string.IsNullOrEmpty(branchCode) ? AdministrationHelper.GetLoggedInUserBranchCode(db, userName) : branchCode;
            var branches = AdministrationHelper.GetBranchDropDownListByUserRole(db, userName);

            var agents = db.Agents.Where(w => w.BranchCode == branchCode);
            agentID = agentID.HasValue ? agentID : agents.Any() ? agents.FirstOrDefault().ID : 0;

            searchText = string.IsNullOrEmpty(searchText) ? string.Empty : searchText;

            model.SelectedBranchCode = branchCode;
            model.Branches = branches;
            model.Agents = AdministrationHelper.GetAgentDropDownList(agents);
            model.SelectedAgentID = agentID.GetValueOrDefault();
            model.SelectedAgentName = AdministrationHelper.GetAgentName(agentID.GetValueOrDefault());
            model.SearchText = searchText;
            model.AgentItineraries = GetAgentItineraryItems(DateTime.Now, branchCode, agentID, searchText);

            decimal shortOverPlus = 0;
            decimal shortOverMinus = 0;

            var shortOverPlusList = model.AgentItineraries.Where(w => w.ShortOver >= 0);
            var shortOverMinusList = model.AgentItineraries.Where(w => w.ShortOver < 0);

            if (shortOverPlusList.Any())
                shortOverPlus = shortOverPlusList.Sum(s => s.ShortOver);

            if (shortOverMinusList.Any())
                shortOverMinus = shortOverMinusList.Sum(s => s.ShortOver);

            model.ShortOverPlus = shortOverPlus;
            model.ShortOverMinus = shortOverMinus;

            return model;
            
        }

        public static CollectionReportModel GetCollectionReportModel(DatabaseContext db, string userName, string branchCode, bool? filterByAgent, int? agentID, int? paymentTypeID, DateTime? fromDate, DateTime? toDate, bool? isDailyReport, DateTime? date)
        {
            branchCode = string.IsNullOrEmpty(branchCode) ? AdministrationHelper.GetLoggedInUserBranchCode(db, userName) : branchCode;
            filterByAgent = filterByAgent.HasValue ? filterByAgent : true;
            var agents = db.Agents.Where(w => w.BranchCode == branchCode);

            fromDate = fromDate.HasValue ? fromDate : DateTime.Today;
            toDate = toDate.HasValue ? toDate : DateTime.Today;
            date = date.HasValue ? date : DateTime.Today;
            isDailyReport = isDailyReport.HasValue ? isDailyReport : false;

            if (isDailyReport.GetValueOrDefault())
            {
                fromDate = date;
                toDate = date;
            }

            if (filterByAgent.GetValueOrDefault() == true)
            {
                agentID = (agentID.HasValue && agentID > 0) ? agentID : agents.Any() ? agents.FirstOrDefault().ID : 0; 
            }
            else
            { agentID = 0; }


            var paymentTypes = AdministrationHelper.GetPaymentTypeDropDownList(db.PaymentTypes).ToList();
            paymentTypes.Insert(0, new SelectListItem() { Value = "0", Text = "All" });
            paymentTypeID = paymentTypeID.HasValue ? paymentTypeID : 0;

            var collectionModel = new CollectionReportModel();
            collectionModel.Agents = AdministrationHelper.GetAgentDropDownList(agents);
            collectionModel.SelectedAgentID = agentID.GetValueOrDefault();
            collectionModel.PaymentTypes = paymentTypes;
            collectionModel.SelectedPaymentTypeID = paymentTypeID.GetValueOrDefault();
            collectionModel.Branches = AdministrationHelper.GetBranchDropDownListByUserRole(db, userName);
            collectionModel.SelectedBranchCode = branchCode;
            collectionModel.IsAgentSelected = agentID > 0;
            collectionModel.FromDate = fromDate.GetValueOrDefault();
            collectionModel.ToDate = toDate.GetValueOrDefault();
            collectionModel.Date = date.GetValueOrDefault();
            collectionModel.IsDailyReport = isDailyReport.GetValueOrDefault();
            collectionModel.IsRangeReport = !isDailyReport.GetValueOrDefault();
            collectionModel.CollectionReportItems = GetCollectionReportItems(branchCode, filterByAgent.GetValueOrDefault(), agentID.GetValueOrDefault(), paymentTypeID.GetValueOrDefault(), fromDate.GetValueOrDefault(), toDate.GetValueOrDefault());

            decimal totalPayment = 0;
            decimal totalPenalty = 0;
            if (collectionModel.CollectionReportItems.Any())
            {
                totalPayment = collectionModel.CollectionReportItems.Sum(s => s.PostedPayment);
                totalPenalty = collectionModel.CollectionReportItems.Sum(s => s.Penalty);
            }

            collectionModel.TotalPenalty = totalPenalty;
            collectionModel.TotalPayment = totalPayment;
            collectionModel.TotalCollection = totalPayment + totalPenalty;

            return collectionModel;
        }

        public static SalesReportModel GetSalesReportModel(DatabaseContext db, string userName, string branchCode, DateTime? fromDate, DateTime? toDate)
        {
            branchCode = string.IsNullOrEmpty(branchCode) ? AdministrationHelper.GetLoggedInUserBranchCode(db, userName) : branchCode;
            fromDate = fromDate.HasValue ? fromDate : DateTime.Today;
            toDate = toDate.HasValue ? toDate : DateTime.Today;

            var salesReportModel = new SalesReportModel();
            salesReportModel.Branches = AdministrationHelper.GetBranchDropDownListByUserRole(db, userName);
            salesReportModel.SelectedBranchCode = branchCode;
            salesReportModel.FromDate = fromDate.GetValueOrDefault();
            salesReportModel.ToDate = toDate.GetValueOrDefault();
            salesReportModel.SalesReportItems = GetSalesReportItems(branchCode, fromDate.GetValueOrDefault(), toDate.GetValueOrDefault());
            salesReportModel.TotalLoanAmount = salesReportModel.SalesReportItems.Sum(s => s.LoanAmount);
            salesReportModel.TotalLoanDeduction = salesReportModel.SalesReportItems.Sum(s => s.TotalDeduction);
            return salesReportModel;

        }

        public static ChequeReportModel GetChequeReportModel(DatabaseContext db, string userName, string branchCode, DateTime? fromDate, DateTime? toDate, int? loanStatusID, string searchText)
        {
            branchCode = string.IsNullOrEmpty(branchCode) ? AdministrationHelper.GetLoggedInUserBranchCode(db, userName) : branchCode;
            fromDate = fromDate.HasValue ? fromDate : DateTime.Today;
            toDate = toDate.HasValue ? toDate : DateTime.Today;
            loanStatusID = loanStatusID.HasValue ? loanStatusID : (int)AdministrationHelper.LoanStatus.None;
            searchText = string.IsNullOrEmpty(searchText) ? string.Empty : searchText;

            var chequeModel = new ChequeReportModel();
            chequeModel.Branches = AdministrationHelper.GetBranchDropDownListByUserRole(db, userName);
            chequeModel.LoanStatuses = AdministrationHelper.GetLoanStatusDropDownList();

            chequeModel.SelectedBranchCode = branchCode;
            chequeModel.FromDate = fromDate.GetValueOrDefault();
            chequeModel.ToDate = toDate.GetValueOrDefault();
            chequeModel.SelectedLoanStatus = loanStatusID.GetValueOrDefault();
            chequeModel.SearchText = searchText;
            chequeModel.ChequeReportItems = GetChequeReportItems(branchCode, fromDate.GetValueOrDefault(), toDate.GetValueOrDefault(), loanStatusID.GetValueOrDefault(), searchText);

            chequeModel.TotalLoanAmount = chequeModel.ChequeReportItems.Sum(s => s.LoanAmount);
            chequeModel.TotalReceivable = chequeModel.ChequeReportItems.Sum(s => s.Receivable);
            chequeModel.TotalChequeAmount = chequeModel.ChequeReportItems.Sum(s => s.ChequeAmount);
            chequeModel.TotalDeduction = chequeModel.ChequeReportItems.Sum(s => s.TotalDeduction);

            return chequeModel;

        }

        public static SavingsReportModel GetSavingsReportModel(DatabaseContext db, string userName, string branchCode, DateTime? dateFrom, DateTime? dateTo, string searchText)
        {
            var model = new SavingsReportModel();

            branchCode = string.IsNullOrEmpty(branchCode) ? AdministrationHelper.GetLoggedInUserBranchCode(db, userName) : branchCode;
            dateFrom = dateFrom.HasValue ? dateFrom : DateTime.Today;
            dateTo = dateTo.HasValue ? dateTo : DateTime.Today;
            searchText = string.IsNullOrEmpty(searchText) ? string.Empty : searchText;

            var branches = db.Branches;
            model.Branches = AdministrationHelper.GetBranchDropDownList(branches);
            model.DateFrom = dateFrom.GetValueOrDefault();
            model.DateTo = dateTo.GetValueOrDefault();
            model.SearchText = searchText;

            model.SelectedBranchCode = branchCode;
            model.SavingsReportItems = GetSavingsReportItems(branchCode, dateFrom.GetValueOrDefault(), dateTo.GetValueOrDefault(), searchText);

            decimal totalSavings = 0;
            if (model.SavingsReportItems.Any())
                totalSavings = model.SavingsReportItems.Sum(s => s.Amount);

            model.TotalSavings = totalSavings;
            return model;
        }

        public static SOAModel GetSOAModel(DatabaseContext db, string userName, string branchCode, DateTime? fromDate, DateTime? toDate)
        {
            branchCode = string.IsNullOrEmpty(branchCode) ? AdministrationHelper.GetLoggedInUserBranchCode(db, userName) : branchCode;
            var branches = db.Branches;
            fromDate = fromDate.HasValue ? fromDate : DateTime.Today;
            toDate = toDate.HasValue ? toDate : DateTime.Today;

            var model = new SOAModel();
            model.FromDate = fromDate.GetValueOrDefault();
            model.ToDate = toDate.GetValueOrDefault();
            model.SelectedBranchCode = branchCode;
            model.Branches = AdministrationHelper.GetBranchDropDownList(branches);
            model.SOAItems = GetSOAItems(branchCode, fromDate.GetValueOrDefault(), toDate.GetValueOrDefault());

            return model;
        }

        public static DuePaymentModel GetDuePaymentModel(DatabaseContext db, string branchCode, int? agentID, string searchText, DateTime? dueDate, string userName)
        {
            branchCode = (string.IsNullOrEmpty(branchCode)) ? AdministrationHelper.GetLoggedInUserBranchCode(db, userName) : branchCode;
            dueDate = dueDate.HasValue ? dueDate : DateTime.Today;

            var agents = db.Agents.Where(w => w.BranchCode == branchCode).ToList();

            var selectedAgentID = 0;

            if (agentID.HasValue)
            {
                selectedAgentID = agentID.Value;
            }
            else
            {
                if (agents.Any())
                {
                    selectedAgentID = agents.FirstOrDefault().ID;
                }
            }

            var paymentTypes = db.PaymentTypes;

            var model = new DuePaymentModel();

            var agentList = AdministrationHelper.GetAgentDropDownList(agents).ToList();
            agentList.Insert(0, new SelectListItem() { Value = "0", Text = "All" });

            model.Agents = agentList;
            model.Branches = AdministrationHelper.GetBranchDropDownListByUserRole(db, userName);

            model.SelectedBranchCode = branchCode;
            model.SelectedAgentID = selectedAgentID;
            model.DueDate = dueDate.GetValueOrDefault();
            model.SearchText = searchText;
            model.IsAdmin = AdministrationHelper.GetLoggedInUserRoleID(db, userName) == (int)AdministrationHelper.Role.Admin;

            var dueItems = GetDueItems(dueDate.GetValueOrDefault(), model.SelectedBranchCode, model.SelectedAgentID, model.SearchText);

            model.DailyDuePaymentItems = dueItems.Where(w => w.ProceedTypeID == (int)AdministrationHelper.ProceedType.Daily).ToList();
            model.OtherDuePaymentItems = dueItems.Where(w => w.ProceedTypeID != (int)AdministrationHelper.ProceedType.Daily).ToList();

            return model;
        }

        public static DailyDuePaymentModel GetDailyDuePaymentModel(DatabaseContext db, string branchCode, int? agentID, DateTime? fromDate, DateTime? toDate, string searchText, string userName)
        {
            branchCode = (string.IsNullOrEmpty(branchCode)) ? AdministrationHelper.GetLoggedInUserBranchCode(db, userName) : branchCode;

            var agents = db.Agents.Where(w => w.BranchCode == branchCode).ToList();

            agentID = agentID.HasValue ? agentID : 0;
            var agentList = AdministrationHelper.GetAgentDropDownList(agents).ToList();
            agentList.Insert(0, new SelectListItem() { Value = "0", Text = "All" });

            fromDate = fromDate.HasValue ? fromDate : DateTime.Today.AddDays(-7);
            toDate = toDate.HasValue ? toDate : DateTime.Today;

            var model = new DailyDuePaymentModel();
            model.FromDate = fromDate;
            model.ToDate = toDate;
            model.SelectedBranchCode = branchCode;
            model.Branches = AdministrationHelper.GetBranchDropDownListByUserRole(db, userName);
            model.SelectedAgentID = agentID.GetValueOrDefault();
            model.Agents = agentList;
            model.SearchText = searchText;
            model.IsAdmin = AdministrationHelper.GetLoggedInUserRoleID(db, userName) == (int)AdministrationHelper.Role.Admin;
            model.DailyDuePaymentItems = GetDailyDuePaymentItems(fromDate.GetValueOrDefault(), toDate.GetValueOrDefault(), branchCode, agentID.GetValueOrDefault(), searchText);

            return model;
        }

        public static OtherDuePaymentModel GetOtherDuePaymentModel(DatabaseContext db, string branchCode, int? agentID, DateTime? fromDate, DateTime? toDate, string searchText, string userName)
        {
            branchCode = (string.IsNullOrEmpty(branchCode)) ? AdministrationHelper.GetLoggedInUserBranchCode(db, userName) : branchCode;

            var agents = db.Agents.Where(w => w.BranchCode == branchCode).ToList();

            agentID = agentID.HasValue ? agentID : 0;
            var agentList = AdministrationHelper.GetAgentDropDownList(agents).ToList();
            agentList.Insert(0, new SelectListItem() { Value = "0", Text = "All" });

            fromDate = fromDate.HasValue ? fromDate : DateTime.Today.AddDays(-7);
            toDate = toDate.HasValue ? toDate : DateTime.Today;

            var model = new OtherDuePaymentModel();
            model.FromDate = fromDate;
            model.ToDate = toDate;
            model.SelectedBranchCode = branchCode;
            model.Branches = AdministrationHelper.GetBranchDropDownListByUserRole(db, userName);
            model.SelectedAgentID = agentID.GetValueOrDefault();
            model.Agents = agentList;
            model.SearchText = searchText;
            model.IsAdmin = AdministrationHelper.GetLoggedInUserRoleID(db, userName) == (int)AdministrationHelper.Role.Admin;
            model.OtherDuePaymentItems = GetOtherDuePaymentItems(fromDate.GetValueOrDefault(), toDate.GetValueOrDefault(), branchCode, agentID.GetValueOrDefault(), searchText);

            return model;
        }

        public static List<DailyDuePaymentItem> GetDailyDuePaymentItems(DateTime fromDate, DateTime toDate, string branchCode, int agentID, string searchText)
        {
            var items = new List<DailyDuePaymentItem>();

            using (var db = new DatabaseContext())
            {
                var paramFromDate = new SqlParameter("@FromDate", fromDate);
                var paramToDate = new SqlParameter("@ToDate", toDate);
                var paramBranchCode = new SqlParameter("@BranchCode", branchCode);
                var paramAgentID = new SqlParameter("@AgentID", agentID);
                var paramSearchText = new SqlParameter("@SearchText", searchText);

                items = db.Database
                    .SqlQuery<DailyDuePaymentItem>("sp_GetDailyDuesPerSchedule @FromDate, @ToDate, @BranchCode, @AgentID , @SearchText",
                                                                    paramFromDate, paramToDate, paramBranchCode, paramAgentID, paramSearchText)
                    .ToList();
            }

            return items;
        }

        public static List<OtherDuePaymentItem> GetOtherDuePaymentItems(DateTime fromDate, DateTime toDate, string branchCode, int agentID, string searchText)
        {
            var items = new List<OtherDuePaymentItem>();

            using (var db = new DatabaseContext())
            {
                var paramFromDate = new SqlParameter("@FromDate", fromDate);
                var paramToDate = new SqlParameter("@ToDate", toDate);
                var paramBranchCode = new SqlParameter("@BranchCode", branchCode);
                var paramAgentID = new SqlParameter("@AgentID", agentID);
                var paramSearchText = new SqlParameter("@SearchText", searchText);
                var paramIsDaily = new SqlParameter("@IsDaily", false);

                items = db.Database
                    .SqlQuery<OtherDuePaymentItem>("sp_GetDailyDuesPerSchedule @FromDate, @ToDate, @BranchCode, @AgentID , @SearchText, @IsDaily",
                                                                    paramFromDate, paramToDate, paramBranchCode, paramAgentID, paramSearchText, paramIsDaily)
                    .ToList();
            }

            return items;
        }

        public static List<DuePaymentItem> GetDueItems(DateTime toDate, string branchCode, int agentID, string searchText)
        {
            var items = new List<DuePaymentItem>();

            using (var db = new DatabaseContext())
            {
                var paramDate = new SqlParameter("@ToDate", toDate);
                var paramBranchCode = new SqlParameter("@BranchCode", branchCode);
                var paramAgentID = new SqlParameter("@AgentID", agentID);
                var paramSearchText = new SqlParameter("@SearchText", searchText);

                items = db.Database
                    .SqlQuery<DuePaymentItem>("sp_GetLoansDueForPayment @ToDate, @BranchCode, @AgentID , @SearchText",
                                                                    paramDate, paramBranchCode, paramAgentID, paramSearchText)
                    .ToList();
            }

            return items;
        }

        public static CollectionReconciliationReportModel GetCollectionReconciliationReportModel(DatabaseContext db, string branchCode, int? agentID, DateTime? collectionDate, string userName)
        {
            branchCode = string.IsNullOrEmpty(branchCode) ? AdministrationHelper.GetLoggedInUserBranchCode(db, userName) : branchCode;
            agentID = agentID.HasValue ? agentID : 0;
            collectionDate = collectionDate.HasValue ? collectionDate : DateTime.Today;

            var model = new CollectionReconciliationReportModel();
            var agents = db.Agents.Where(w => w.BranchCode == branchCode);
            var agentList = AdministrationHelper.GetAgentDropDownList(agents).ToList();
            agentList.Insert(0, new SelectListItem() { Value = "0", Text = "All" });

            model.Branches = AdministrationHelper.GetBranchDropDownList(db.Branches);
            model.SelectedBranchCode = branchCode;
            model.Agents = agentList;
            model.SelectedAgentID = agentID.GetValueOrDefault();
            model.CollectionDate = collectionDate.GetValueOrDefault();

            model.CollectionOnRecordItems = GetCollectionOnRecordItems(branchCode, agentID, collectionDate);
            model.CollectionReconciliationItems = GetCollectionReconciliationItems(branchCode, agentID, collectionDate);

            model.TotalCollectionPerRecord = model.CollectionOnRecordItems.Sum(s => s.PostedPayment) + model.CollectionOnRecordItems.Sum(s => s.Penalty);

            model.TotalCashDeposited = model.CollectionReconciliationItems.Sum(s => s.CashDeposited);
            model.TotalDeductedToChequeAdvancePayment = model.CollectionReconciliationItems.Sum(s => s.DeductedToChequeAdvancePayment);
            model.TotalDeductedToChequeSavings = model.CollectionReconciliationItems.Sum(s => s.DeductedToChequeSavings);
            model.TotalUsedForLoanRelease = model.CollectionReconciliationItems.Sum(s => s.UsedForLoanRelease);
            model.TotalUsedInWithdrawalOfSavings = model.CollectionReconciliationItems.Sum(s => s.UsedInWithdrawalOfSavings);
            model.TotalOthers = model.CollectionReconciliationItems.Sum(s => s.Others);

            model.TotalActualCashCollection = model.TotalCollectionPerRecord
                                                - model.TotalCashDeposited
                                                - model.TotalDeductedToChequeAdvancePayment
                                                - model.TotalDeductedToChequeSavings
                                                - model.TotalUsedForLoanRelease
                                                - model.TotalUsedInWithdrawalOfSavings
                                                - model.TotalOthers;

            model.TotalShortOver = model.TotalActualCashCollection - model.TotalCollectionPerRecord;

            return model;

        }

        public static AgingReportModel GetAgingReportModel(DatabaseContext db, string branchCode, int? agentID, DateTime? fromDate, DateTime? toDate, string userName, int days = 0)
        {
            branchCode = (string.IsNullOrEmpty(branchCode)) ? AdministrationHelper.GetLoggedInUserBranchCode(db, userName) : branchCode;

            var agents = db.Agents.Where(w => w.BranchCode == branchCode).ToList();

            agentID = agentID.HasValue ? agentID : 0;
            var agentList = AdministrationHelper.GetAgentDropDownList(agents).ToList();
            agentList.Insert(0, new SelectListItem() { Value = "0", Text = "All" });

            fromDate = fromDate.HasValue ? fromDate : DateTime.Today.AddDays(-7);
            toDate = toDate.HasValue ? toDate : DateTime.Today;

            var model = new AgingReportModel();
            model.Branches = AdministrationHelper.GetBranchDropDownList(db.Branches);
            model.Agents = agentList;
            model.SelectedBranchCode = branchCode;
            model.SelectedAgentID = agentID.GetValueOrDefault();
            model.FromDate = fromDate.GetValueOrDefault();
            model.ToDate = toDate.GetValueOrDefault();
            model.Days = AdministrationHelper.GetAgingDayList();
            model.SelectedDays = days;
            model.AgingItems = GetAgingReportItems(branchCode, agentID.GetValueOrDefault(), fromDate.GetValueOrDefault(), toDate.GetValueOrDefault(), days);
            return model;

        }

        public static List<AgingReportItem> GetAgingReportItems(string branchCode, int agentID, DateTime fromDate, DateTime toDate, int days)
        {
            var items = new List<AgingReportItem>();

            using (var db = new DatabaseContext())
            {
                var paramBranchCode = new SqlParameter("@BranchCode", branchCode);
                var paramAgentID = new SqlParameter("@AgentID", agentID);
                var paramFromDate = new SqlParameter("@FromDate", fromDate);
                var paramToDate = new SqlParameter("@ToDate", toDate);
                var paramDays = new SqlParameter("@Days", days);

                items = db.Database
                    .SqlQuery<AgingReportItem>("sp_GetAgingReport @BranchCode, @AgentID , @FromDate, @ToDate, @Days",
                                                                    paramBranchCode, paramAgentID, paramFromDate, paramToDate, paramDays)
                    .ToList();
            }

            return items;
        }

        #region Query Models

        public static List<AgentItinerary> GetAgentItineraryItems(DateTime toDate, string branchCode, int? agentID, string searchText)
        {
            var queryModel = new List<AgentItinerary>();

            using (var db = new DatabaseContext())
            {
                var paramToDate = new SqlParameter("@ToDate", toDate);
                var paramBranchCode = new SqlParameter("@BranchCode", branchCode);
                var paramAgentID = new SqlParameter("@AgentID", agentID.GetValueOrDefault());
                var paramSearchText= new SqlParameter("@SearchText", searchText);

                queryModel = db.Database
                    .SqlQuery<AgentItinerary>("sp_GetAgentItinerary @ToDate, @BranchCode, @AgentID, @SearchText",
                                                            paramToDate, paramBranchCode, paramAgentID, paramSearchText)
                    .ToList();
            }

            return queryModel;
        }

        public static List<CollectionReportItem> GetCollectionReportItems(string branchCode, bool filterByAgent, int? agentID, int? paymentTypeID, DateTime fromDate, DateTime toDate)
        {
            var queryModel = new List<CollectionReportItem>();

            using (var db = new DatabaseContext())
            {
                var paramBranchCode = new SqlParameter("@BranchCode", branchCode);
                var paramFilterByAgent = new SqlParameter("@FilterByAgent", filterByAgent);
                var paramAgentID = new SqlParameter("@AgentID", agentID);
                var paramPaymentTypeID = new SqlParameter("@PaymentTypeID", paymentTypeID);
                var paramFromDate = new SqlParameter("@FromDate", fromDate);
                var paramToDate = new SqlParameter("@ToDate", toDate);

                queryModel = db.Database
                    .SqlQuery<CollectionReportItem>("sp_GetCollectionReport @BranchCode, @FilterByAgent, @AgentID, @PaymentTypeID, @FromDate, @ToDate", 
                                                            paramBranchCode, paramFilterByAgent, paramAgentID, paramPaymentTypeID, paramFromDate, paramToDate)
                    .ToList();
            }

            return queryModel;
        
        }

        public static List<SalesReportItem> GetSalesReportItems(string branchCode, DateTime fromDate, DateTime toDate)
        {
            var queryModel = new List<SalesReportItem>();

            using (var db = new DatabaseContext())
            {
                var paramBranchCode = new SqlParameter("@BranchCode", branchCode);
                var paramFromDate = new SqlParameter("@FromDate", fromDate);
                var paramToDate = new SqlParameter("@ToDate", toDate);

                queryModel = db.Database
                    .SqlQuery<SalesReportItem>("sp_GetSalesReport @BranchCode, @FromDate, @ToDate",
                                                            paramBranchCode, paramFromDate, paramToDate)
                    .ToList();
            }

            return queryModel;

        }

        public static List<ChequeReportItem> GetChequeReportItems(string branchCode, DateTime fromDate, DateTime toDate, int loanStatusID, string searchText)
        {
            var queryModel = new List<ChequeReportItem>();

            using (var db = new DatabaseContext())
            {
                var paramBranchCode = new SqlParameter("@BranchCode", branchCode);
                var paramFromDate = new SqlParameter("@FromDate", fromDate);
                var paramToDate = new SqlParameter("@ToDate", toDate);
                var paramLoanStatusID = new SqlParameter("@LoanStatusID", loanStatusID);
                var paramSearchText = new SqlParameter("@SearchText", searchText);

                queryModel = db.Database
                    .SqlQuery<ChequeReportItem>("sp_GetChequeReport @BranchCode, @FromDate, @ToDate, @LoanStatusID, @SearchText",
                                                            paramBranchCode, paramFromDate, paramToDate, paramLoanStatusID, paramSearchText)
                    .ToList();
            }

            return queryModel;

        }

        public static List<SavingsReportItem> GetSavingsReportItems(string branchCode, DateTime dateFrom, DateTime dateTo, string searchText)
        {
            var queryModel = new List<SavingsReportItem>();

            using (var db = new DatabaseContext())
            {
                var paramBranchCode = new SqlParameter("@BranchCode", branchCode);
                var paramDateFrom = new SqlParameter("@DateFrom", dateFrom);
                var paramDateTo = new SqlParameter("@DateTo", dateTo);
                var paramSearchText = new SqlParameter("@SearchText", searchText.Trim());
                
                queryModel = db.Database
                    .SqlQuery<SavingsReportItem>("sp_GetSavingsReport @BranchCode, @DateFrom, @DateTo, @SearchText",
                                                            paramBranchCode, paramDateFrom, paramDateTo, paramSearchText)
                    .ToList();
            }

            return queryModel;
        }

        public static List<SOAItem> GetSOAItems(string branchCode, DateTime fromDate, DateTime toDate)
        {
            var queryModel = new List<SOAItem>();

            using (var db = new DatabaseContext())
            {
                var paramBranchCode = new SqlParameter("@BranchCode", branchCode);
                var paramFromDate = new SqlParameter("@FromDate", fromDate);
                var paramToDate = new SqlParameter("@ToDate", toDate);

                queryModel = db.Database
                    .SqlQuery<SOAItem>("sp_GetSOAReport @BranchCode, @FromDate, @ToDate",
                                                            paramBranchCode, paramFromDate, paramToDate)
                    .ToList();
            }

            return queryModel;
        }

        public static PrintSOAModel GetPrintSOAModel(string documentNo)
        {
            var model = new PrintSOAModel();

            using (var db = new DatabaseContext())
            {
                var param = new SqlParameter("@DocumentNo", documentNo);
                
                model = db.Database
                    .SqlQuery<PrintSOAModel>("sp_GetSOAReportItem @DocumentNo", param)
                    .SingleOrDefault<PrintSOAModel>();

                var loan = FundingHelper.GetLoanQueryModel(documentNo);
                model.Payments = FundingHelper.GetScheduleVsPaymentModel(db, loan, AdministrationHelper.ProceedType.Daily);
            }

            return model;
        }

        public static PenaltyHistoryModel GetPenaltyHistoryModel(DatabaseContext db, string branchCode, DateTime? dateFrom, DateTime? dateTo, string searchText, string userName)
        {
            branchCode = string.IsNullOrEmpty(branchCode) ? AdministrationHelper.GetLoggedInUserBranchCode(db, userName) : branchCode;
            dateFrom = dateFrom.HasValue ? dateFrom : DateTime.Today;
            dateTo = dateTo.HasValue ? dateTo : DateTime.Today;
            searchText = string.IsNullOrEmpty(searchText) ? string.Empty : searchText;

            var model = new PenaltyHistoryModel();
            model.Branches = AdministrationHelper.GetBranchDropDownList(db.Branches);
            model.SelectedBranchCode = branchCode;
            model.SearchText = searchText;
            model.LoanDateFrom = dateFrom.GetValueOrDefault();
            model.LoanDateTo = dateTo.GetValueOrDefault();
            
            model.PenaltyItems = GetPenaltyHistoryList(branchCode, dateFrom.GetValueOrDefault(), dateTo.GetValueOrDefault(), searchText);
            model.PenaltyPaymentItems = GetPenaltyPaymentHistoryList(branchCode, dateFrom.GetValueOrDefault(), dateTo.GetValueOrDefault(), searchText);

            decimal totalPenalty = 0;
            if (model.PenaltyItems.Any())
                totalPenalty = model.PenaltyItems.Sum(s => s.Penalty);

            model.TotalPenalty = totalPenalty;

            decimal totalPaidPenalty = 0;
            if (model.PenaltyPaymentItems.Any())
                totalPaidPenalty = model.PenaltyPaymentItems.Sum(s => s.PaidPenaltyAmount);

            model.TotalPenaltyPayment = totalPaidPenalty;

            return model;
        }

        public static List<LoanPenaltyHistoryItem> GetPenaltyHistoryList(string branchCode, DateTime dateFrom, DateTime dateTo, string searchText)
        {
            var model = new List<LoanPenaltyHistoryItem>();

            using (var db = new DatabaseContext())
            {
                var paramBranchCode = new SqlParameter("@BranchCode", branchCode);
                var paramDateFrom = new SqlParameter("@DateFrom", dateFrom);
                var paramDateTo = new SqlParameter("@DateTo", dateTo);
                var paramSearchText = new SqlParameter("@SearchText", searchText.Trim());

                model = db.Database
                    .SqlQuery<LoanPenaltyHistoryItem>("sp_GetPenaltyHistory @BranchCode, @DateFrom, @DateTo, @SearchText",
                                                            paramBranchCode, paramDateFrom, paramDateTo, paramSearchText)
                    .ToList();
            }

            return model;
        }

        public static List<LoanPenaltyPaymentHistoryItem> GetPenaltyPaymentHistoryList(string branchCode, DateTime dateFrom, DateTime dateTo, string searchText)
        {
            var model = new List<LoanPenaltyPaymentHistoryItem>();

            using (var db = new DatabaseContext())
            {
                var paramBranchCode = new SqlParameter("@BranchCode", branchCode);
                var paramDateFrom = new SqlParameter("@DateFrom", dateFrom);
                var paramDateTo = new SqlParameter("@DateTo", dateTo);
                var paramSearchText = new SqlParameter("@SearchText", searchText.Trim());

                model = db.Database
                    .SqlQuery<LoanPenaltyPaymentHistoryItem>("sp_GetPenaltyPaymentHistory @BranchCode, @DateFrom, @DateTo, @SearchText",
                                                            paramBranchCode, paramDateFrom, paramDateTo, paramSearchText)
                    .ToList();
            }

            return model;
        }

        public static ClosedLoansReportModel GetClosedLoansReportModel(DatabaseContext db, string branchCode, DateTime? dateFrom, DateTime? dateTo, string searchText, string userName)
        {
            branchCode = string.IsNullOrEmpty(branchCode) ? AdministrationHelper.GetLoggedInUserBranchCode(db, userName) : branchCode;
            dateFrom = dateFrom.HasValue ? dateFrom : DateTime.Today;
            dateTo = dateTo.HasValue ? dateTo : DateTime.Today;
            searchText = string.IsNullOrEmpty(searchText) ? string.Empty : searchText;

            var model = new ClosedLoansReportModel();
            model.Branches = AdministrationHelper.GetBranchDropDownList(db.Branches);
            model.SelectedBranchCode = branchCode;
            model.SearchText = searchText;
            model.DateClosedFrom = dateFrom.GetValueOrDefault();
            model.DateClosedTo = dateTo.GetValueOrDefault();

            var loans = db.Loans.Where(w => w.StatusID == (int)AdministrationHelper.LoanStatus.Closed
                                        && w.BranchCode == branchCode
                                        && w.DateClosed != null
                                        && w.DateClosed >= dateFrom
                                        && w.DateClosed <= dateTo).ToList();
            
            var docs = loans.Select(s => s.DocumentNo);
            var payments = db.LoanPayments.Where(w => w.IsVoid == false
                                            && w.StatusID == (int)AdministrationHelper.LoanPaymentStatus.Posted
                                            && docs.Contains(w.DocumentNo)).ToList();

            model.ClosedLoanReportItems = ClosedLoanReportItems(loans, payments, branchCode, dateFrom, dateTo, searchText);

            return model;
        }

        public static List<ClosedLoanReportItem> ClosedLoanReportItems(List<Loan> loans, List<LoanPayment> payments, string branchCode, DateTime? dateFrom, DateTime? dateTo, string searchText)
        {
            var items = new List<ClosedLoanReportItem>();

            foreach (var loan in loans)
            {
                var item = new ClosedLoanReportItem();
                item.BorrowerID = loan.BorrowerID;
                item.BorrowerName = AdministrationHelper.GetBorrowerName(loan.BorrowerID);
                item.ContactNo = AdministrationHelper.GetBorrowerContactNumbers(loan.BorrowerID);
                item.DocumentNo = loan.DocumentNo;
                item.DocumentDate = loan.DocumentDate;
                item.AgentName = AdministrationHelper.GetAgentName(loan.AgentID);
                item.Remarks = ((WorkspaceHelper.CloseLoanRemark)loan.RemarkID).ToDescription();
                item.StatusName = AdministrationHelper.LoanStatus.Closed.ToString();
                item.Receivable = loan.LoanAmount + (loan.LoanAmount * loan.LoanInterest / 100);
                item.TotalPayment = 0;

                var pay = payments.Where(w => w.DocumentNo == loan.DocumentNo);
                if (pay.Any())
                    item.TotalPayment = pay.Sum(s => s.Amount);
                
                items.Add(item);
            }

            var returnItems = items.Where(w => w.DocumentNo.Contains(searchText) 
                                    || w.BorrowerName.Contains(searchText) 
                                    || w.AgentName.Contains(searchText)
                                    || w.Remarks.Contains(searchText)).ToList();

            return returnItems;
        }

        public static List<CollectionOnRecord> GetCollectionOnRecordItems(string branchCode, int? agentID, DateTime? collectionDate)
        {
            var items = new List<CollectionOnRecord>();

            using (var db = new DatabaseContext())
            {
                var paramBranchCode = new SqlParameter("@BranchCode", branchCode);
                var paramAgentID = new SqlParameter("@AgentID", agentID);
                var paramCollectionDate = new SqlParameter("@CollectionDate", collectionDate);

                items = db.Database
                    .SqlQuery<CollectionOnRecord>("sp_GetCollectionOnRecord @BranchCode, @AgentID, @CollectionDate",
                                                            paramBranchCode, paramAgentID, paramCollectionDate)
                    .ToList();
            }

            return items;
        }

        public static List<CollectionReconciliationQueryModel> GetCollectionReconciliationItems(string branchCode, int? agentID, DateTime? collectionDate)
        {
            var items = new List<CollectionReconciliationQueryModel>();

            using (var db = new DatabaseContext())
            {
                var paramBranchCode = new SqlParameter("@BranchCode", branchCode);
                var paramAgentID = new SqlParameter("@AgentID", agentID);
                var paramCollectionDate = new SqlParameter("@CollectionDate", collectionDate);

                items = db.Database
                    .SqlQuery<CollectionReconciliationQueryModel>("sp_GetCollectionReconciliationReportItems @BranchCode, @AgentID, @CollectionDate",
                                                            paramBranchCode, paramAgentID, paramCollectionDate)
                    .ToList();
            }

            return items;
        }

        #endregion

        #region Excel

        public static ExcelWorksheet SetWorksheet(ExcelPackage package, string workBookName, string firstHeaderCenteredText, 
            int defaultColumnWidth = 20, eOrientation orientation = eOrientation.Landscape, bool showGridLines = false,
            ePaperSize paperSize = ePaperSize.Legal, bool verticalCentered = false, bool horizontalCentered = true,
            bool pageLayoutView = true, int zoomScale = 90)
        {

            var ws = package.Workbook.Worksheets.Add(workBookName);

            ws.DefaultColWidth = defaultColumnWidth;
            ws.PrinterSettings.Orientation = orientation;
            ws.PrinterSettings.ShowGridLines = showGridLines;
            ws.PrinterSettings.PaperSize = paperSize;
            ws.PrinterSettings.VerticalCentered = verticalCentered;
            ws.PrinterSettings.HorizontalCentered = horizontalCentered;
            ws.View.PageLayoutView = pageLayoutView;
            ws.View.ZoomScale = 90;
            ws.HeaderFooter.FirstHeader.CenteredText = firstHeaderCenteredText;

            return ws;
        }

        public static ExcelWorksheet SetWorksheetHeader(ExcelWorksheet ws, List<string> headerNames,
            char columnStart = 'A', int rowStart = 1, bool setBorder = true)
        {
            var column = columnStart;

            for (var columnValue = 0; columnValue < headerNames.Count(); columnValue++)
            {
                ws.Cells[column + (rowStart).ToString()].Value = headerNames[columnValue];

                ws.Cells[column + (rowStart).ToString()].Style.Fill.PatternType = ExcelFillStyle.Solid;
                ws.Cells[column + (rowStart).ToString()].Style.Fill.BackgroundColor.SetColor(Color.LightGray);

                if (setBorder)
                    ws.Cells[column + (rowStart).ToString()].Style.Border.BorderAround(ExcelBorderStyle.Thin);

                ws.Cells[column + (rowStart).ToString()].AutoFitColumns();

                ++column;
            }

            return ws;
        }

        public static ExcelWorksheet SetWorksheetRows(ExcelWorksheet ws, List<string> headerValues, char nextColumn, int nextRow)
        {
            
            for (var value = 0; value < headerValues.Count(); value++)
            {
                ws.Cells[nextColumn + nextRow.ToString()].Value = headerValues[value];
                ++nextColumn;
            }

            ws.Cells.AutoFitColumns();

            return ws;
        }

        private static bool IsFileLocked(FileInfo file)
        {
            FileStream stream = null;

            try
            {
                stream = file.Open(FileMode.Open, FileAccess.ReadWrite, FileShare.None);
                stream.Close();
            }
            catch (IOException)
            {
                return true;
            }
            finally
            {
                if (stream != null)
                    stream.Close();
            }

            return false;
        }

        #endregion

        #region PDF

        public static Byte[] ExportToPDF(string html, byte[] logo, iTextSharp.text.Rectangle pageSize, float marginLeft = 80f, float marginRight = 80f, float marginTop = 40f, float marginBottom = 80f)
        {
            Byte[] bytes;

            using (var ms = new MemoryStream())
            {
                using (var doc = new Document())
                {
                    using (var writer = PdfWriter.GetInstance(doc, ms))
                    {
                        doc.Open();
                        doc.SetPageSize(pageSize);
                        doc.SetMargins(marginLeft, marginRight, marginTop, marginBottom);
                        doc.NewPage();

                        using (var srHtml = new StringReader(html))
                        {
                            Stream imgStream = new MemoryStream(logo);
                            iTextSharp.text.Image pic = iTextSharp.text.Image.GetInstance(imgStream);
                            pic.Alignment = Element.ALIGN_LEFT;
                            pic.Border = 0;
                            doc.Add(pic);
                            iTextSharp.tool.xml.XMLWorkerHelper.GetInstance().ParseXHtml(writer, doc, srHtml);
                        }

                        doc.Close();
                    }
                }

                bytes = ms.ToArray();
            }

            return bytes;
        }

        public static void ExportToPDF(string html, string path, iTextSharp.text.Rectangle pageSize, float marginLeft = 80f, float marginRight = 80f, float marginTop = 40f, float marginBottom = 80f)
        {
            Byte[] bytes;

            using (var ms = new MemoryStream())
            {
                using (var doc = new Document())
                {
                    using (var writer = PdfWriter.GetInstance(doc, ms))
                    {
                        doc.Open();
                        doc.SetPageSize(pageSize);
                        doc.SetMargins(marginLeft, marginRight, marginTop, marginBottom);
                        doc.NewPage();

                        using (var srHtml = new StringReader(html))
                        {
                            iTextSharp.tool.xml.XMLWorkerHelper.GetInstance().ParseXHtml(writer, doc, srHtml);
                        }

                        doc.Close();
                    }
                }

                bytes = ms.ToArray();
            }

            var newFileInfo = new FileInfo(path);
            if (!newFileInfo.Directory.Exists) Directory.CreateDirectory(newFileInfo.Directory.FullName);
            //try { Array.ForEach(Directory.GetFiles(newFileInfo.Directory.FullName), delegate (string filePath) { File.Delete(filePath); }); } catch { }
            System.IO.File.WriteAllBytes(path, bytes);

        }

        public static string GetHtmlString(HtmlTable table)
        {
            StringBuilder sb = new StringBuilder();
            StringWriter tw = new StringWriter(sb);
            HtmlTextWriter hw = new HtmlTextWriter(tw);

            table.RenderControl(hw);

            return sb.ToString();
        }

        public static HtmlTable GetTable(bool setBorder = true)
        {
            var table = new HtmlTable();

            table.CellPadding = 0;
            table.CellSpacing = 0;
            table.Style.Add("display", "table");
            if (setBorder)
                table.Style.Add("border", "1px solid #EBEBEB");
            table.Style.Add("width", "100%");
            table.Style.Add("border-collapse", "collapse");
            table.Style.Add("font-family", "Helvetica, Arial, sans-serif");
            table.Style.Add("font-size", "11px");
            table.Style.Add("repeat-header", "yes");

            return table;
        }

        public static HtmlTable SetHeaderValues(HtmlTable table, HtmlTableRow header, List<string> headerValues, 
            int columnSpan = 1, int rowSpan = 1, string textAlign = "left", string width = "80px")
        {
            for (var i = 0; i < headerValues.Count(); i++)
            {
                var headerCell = new HtmlTableCell();

                headerCell.InnerText = headerValues[i];

                headerCell.Style.Add("background-color", "#F5F5F6");
                headerCell.Style.Add("border", "1px solid #e7e7e7");
                headerCell.Style.Add("padding", "8px");
                headerCell.Style.Add("text-align", textAlign);
                headerCell.Style.Add("font-weight", "bold");
                headerCell.Style.Add("height", "20px");
                headerCell.Style.Add("width", width);

                headerCell.ColSpan = columnSpan;
                headerCell.RowSpan = rowSpan;

                header.Cells.Add(headerCell);
            }

            table.Rows.Add(header);

            return table;
        }

        public static HtmlTable SetRowValues(HtmlTable table, HtmlTableRow row, List<string> rowValues,
            int columnSpan = 1, int rowSpan = 1, bool setBorder = true, string textAlign = "left", 
            int paddingLeft = 0, int paddingRight = 0, int fontSize = 12)
        {
            for (var i = 0; i < rowValues.Count(); i++)
            {
                var rowCell = new HtmlTableCell();

                rowCell.InnerText = rowValues[i];

                rowCell.Style.Add("height", "20px");
                rowCell.Style.Add("text-align", textAlign);
                rowCell.Style.Add("background-color", "#ffffff");
                rowCell.Style.Add("font-size", fontSize.ToString() + "px");

                if (paddingLeft > 0)
                    rowCell.Style.Add("padding-left", paddingLeft.ToString() + "px");

                if (paddingRight > 0)
                    rowCell.Style.Add("padding-right", paddingRight.ToString() + "px");

                if (setBorder)
                    rowCell.Style.Add("border", "1px solid #e7e7e7");

                

                rowCell.ColSpan = columnSpan;
                rowCell.RowSpan = rowSpan;

                row.Cells.Add(rowCell);

            }

            table.Rows.Add(row);

            return table;
        }

        #endregion

        #region Download

        public static void DownloadFile(string fromServerPath, string toClientPath, string fileName)
        {
            var uri = new Uri(fromServerPath + fileName);
            var webClient = new WebClient();
            webClient.DownloadFileAsync(uri, toClientPath + fileName);
        }

        #endregion

    }
}